package banco.composEstoque;

import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.GregorianCalendar;

import utilidades.FormatarData;
import banco.Banco;
import dominio.Cliente;
import dominio.Historico;
import dominio.composEstoque.Deb_Cred;
import dominio.composEstoque.Demonstrativo_Estoque;
import dominio.composEstoque.ICMS;
import fachada.composEstoque.Fachada_Dem_Estoque;

public class Demonstrativo_Estoque_DAO implements Fachada_Dem_Estoque {

	/* (non-Javadoc)
	 * @see banco.composEstoque.Fachada_Dem_Estoque#insertDem_Estoque(dominio.composEstoque.Total_Demonstrativo_Estoque, dominio.Cliente, dominio.composEstoque.Demonstrativo_Estoque)
	 */
	@SuppressWarnings("finally")
	public void insertDem_Estoque(Cliente c, Demonstrativo_Estoque dem_estoque, String filial) throws ClassNotFoundException, IOException {
	        
	        Connection conn = null;
	        PreparedStatement stmt = null;
	        ResultSet rs = null;
	        
	        try {
	            conn = Banco.getConexaoEXTRATOPAES();
	            stmt = conn.prepareStatement("insert into composestoque_dem_estoque values (?, ?, ?, ?, ?, ?, ?, ?)");
	            stmt.setDate(1, new java.sql.Date(dem_estoque.getData().getTimeInMillis()));
	            stmt.setString(2, dem_estoque.getCodigo().trim());
	            stmt.setFloat(3, dem_estoque.getValor());
	            stmt.setString(4, dem_estoque.getTipo().toString());
	            stmt.setString(5, dem_estoque.getIcms().getDescricao().trim());
	            stmt.setFloat(6, dem_estoque.getIcms().getValor());
	            stmt.setString(7, c.getCnpj().trim());
	            stmt.setString(8, filial);	          
	            stmt.executeUpdate();
	        }
	        catch (SQLException ex) {
	            ex.printStackTrace();
	        }
	        
	        catch (Exception e) {
	        	e.printStackTrace();
	        }
	        finally {
		        Banco.closeConexao(conn, rs, stmt);
	        }
	    }
	
	@SuppressWarnings("finally")
	public Collection selectDem_Estoque(Cliente c, String tipo, String filial, Date mesini, Date mesfim) throws IOException {
		
		Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    Demonstrativo_Estoque de;
	    Collection<Demonstrativo_Estoque> res = new ArrayList<Demonstrativo_Estoque>();
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("select * from composestoque_dem_estoque where cnpj_cliente = ? and tipo = ? and filial = ? and data >= ? and data <= ? order by data, codigo");	        
	        stmt.setString(1, c.getCnpj().trim());
	        stmt.setString(2, tipo);
	        stmt.setString(3, filial);
	        stmt.setDate(4, mesini);
	        stmt.setDate(5, mesfim);
	        rs = stmt.executeQuery();
	        while (rs.next()){
	        	de = new Demonstrativo_Estoque();
	        	de.setCodigo(rs.getString("codigo"));
	        	de.setData(FormatarData.formataDate(rs.getDate("data")));
	        	de.setId(rs.getInt("id"));
	        	de.setValor(rs.getFloat("valor"));
	        	de.setPeriodo(FormatarData.getMesAno(rs.getDate("data")));
	        	de.setTipo(Deb_Cred.setDeb_Cred(rs.getString("tipo")));
	        	de.setIcms(new ICMS(rs.getString("descricao_icms"), rs.getFloat("valor_icms")));
	        	res.add(de);
	        }
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return res;
	    }
	    
	}
	
	public void updateDem_Estoque(Cliente c, Demonstrativo_Estoque dem_estoque, String filial) throws ClassNotFoundException, IOException {
	    
	    Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("update composestoque_dem_estoque set valor = ? where codigo = ? and data = ? and filial = ?");
	        stmt.setFloat(1, dem_estoque.getValor());        
	        stmt.setString(2, dem_estoque.getCodigo().trim());
	        stmt.setString(3, FormatarData.formatar(dem_estoque.getData()));
	        stmt.setString(4, filial);
	        stmt.executeUpdate();
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    
	    catch (Exception e) {
	    	e.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	    }
	}
	
}
